/**
 * Created by ZT on 2018/7/5 0005.
 * node 启动的js，express作为中间件，连接数据库和前台
 */
var mysql = require('mysql');
var express = require('express');
var bodyParser = require('body-parser');
var sqls = require('../server/sqlMap');  //对应的mysql语句
var query =require('../server/db');
var ueditor = require('ueditor');
var path =require('path');
var app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
function setupCORS(req, res, next) {
    res.header('Access-Control-Allow-Methods', 'GET,PUT,POST,DELETE,OPTIONS');
    res.header('Access-Control-Allow-Headers', 'X-Requested-With, Content-type,Accept,X-Access-Token,X-Key');
    res.header('Access-Control-Allow-Origin', '*');
    if (req.method === 'OPTIONS') {
        res.status(200).end();
    } else {
        next();
    }
}
app.all('/*', setupCORS);
app.disable('no cache');

/**
* 公共请求   前后台需要获取文章数据
* */
app.use('/article',function (req,res) {
    //加入數據庫
    var params = req.query;
    var union = `left join zt_content as B on A.content_id=B.id left join zt_category as C on A.category_id=C.id`;
    var fields = 'A.id,B.content,A.title,A.abstr,C.type_name,A.imgUrl,A.create_time,A.top,A.`key`,A.key_value,A.subtitle,A.imgUrl';
    if (params['type'] == 1) {
        var sqlParamsEntity = [];
        var page = params['page'];
        var per_page = params['per_page'];
        var sql1 = sqls.coTableData('zt_article', union, fields, page, per_page);
        var sql2 = sqls.countTableLen('zt_article', '');
        sqlParamsEntity.push(query.getNewSqlParamEntity(sql1, {}));
        sqlParamsEntity.push(query.getNewSqlParamEntity(sql2, {}));
        query.execTrans(sqlParamsEntity, function (err, result) {
            var results = {'ret': 1, 'rows': result[0], 'total': result[1][0]['count(1)']};
            res.send(results);
        });
    } else {
        var id = params['id'];
        var sql = sqls.fidDateFromTable('zt_article', id, union, fields);
        var sqlParamsEntity = [];
        sqlParamsEntity.push(query.getNewSqlParamEntity(sql, {}));
        query.execTrans(sqlParamsEntity, function (err, result) {
            var results = {'ret': 1, 'rows': result};
            res.send(results);
        });
    }
});
/*获取专栏信息*/
app.use('/articlePersonal',function (req,res) {
    var params = req.query;
    var union = `left join zt_content as B on A.content_id=B.id left join zt_category as C on A.category_id=C.id where A.is_reprint=0  order by A.create_time desc`;
    var fields = 'A.id,B.content,A.title,A.abstr,C.type_name,A.imgUrl,A.create_time,A.top,A.`key`,A.key_value,A.subtitle,A.imgUrl';
    var sqlParamsEntity = [];
    var page = params['page'];
    var per_page = params['per_page'];
    var sql1 = sqls.coTableData('zt_article', union, fields, page, per_page);
    var sql2 = sqls.countTableLen('zt_article', '');
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql1, {}));
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql2, {}));
    query.execTrans(sqlParamsEntity, function (err, result) {
        var results = {'ret': 1, 'rows': result[0], 'total': result[1][0]['count(1)']};
        res.send(results);
    });

});

/**
 * 后台请求
 * */
var ueditorConfig = require('../static/ueditor/ueditor.config.json');//公共方
var dirName = path.resolve(__dirname, '..');
app.use('/api/add',ueditor(path.join(dirName, 'static'),function (req,res,next) {
    var imgDir = '/ueditor/images' //默认上传地址为图片
    var ActionType = req.query.action;
    if (ActionType === 'uploadimage' || ActionType === 'uploadfile' || ActionType === 'uploadvideo') {
        var file_url = imgDir;//默认上传地址为图片
        /*其他上传格式的地址*/
        if (ActionType === 'uploadfile') {
            file_url = '/file/ueditor/'; //附件保存地址
        }
        if (ActionType === 'uploadvideo') {
            file_url = '/video/ueditor/'; //视频保存地址
        }
        res.setHeader('Content-Type', 'application/json');
        res.ue_up(file_url); //你只要输入要保存的地址 。保存操作交给ueditor来做
    }
    //客户端发起图片列表请求
    else if (ActionType === 'listimage'){

        res.ue_list(imgDir);  // 客户端会列出 dir_url 目录下的所有图片
    }
    // 客户端发起其它请求
    else {
        res.jsonp(ueditorConfig);
       /* res.setHeader('Content-Type', 'application/json');
        res.redirect('http://127.0.0.1:8081/static/ueditor/nodejs/config.json');*/
    }
}));

/*文章类型获取*/
app.use('/gets',function (req,res) {
    var params = req.query;
    var sql='';
    switch( params['type']){
        case '1':
                sql=sqls.findData('zt_category');
                var sqlParamsEntity = [];
                sqlParamsEntity.push(query.getNewSqlParamEntity(sql,{}));
                query.execTrans(sqlParamsEntity, function(err, result){
                    if(err){
                        console.error("事务执行失败");
                    }else{
                        var results  ={'ret':1,'rows':result[0]};
                        res.send(results);
                    }
                });
            break;
        case '2':
            sql=sqls.findData('zt_key');
            var sqlParamsEntity = [];
            sqlParamsEntity.push(query.getNewSqlParamEntity(sql,{}));
            query.execTrans(sqlParamsEntity, function(err, result){
                if(err){
                    console.error("事务执行失败");
                }else{
                    var results  ={'ret':1,'rows':result[0]};
                    res.send(results);
                }
            });
            break;
    }
});

/**
*提交数据 文章
*
*/
app.post('/posts',function (req,res) {
    var datas = req.body;
    var unions = '';
    var sqlParamsEntity = [];
    var sqlInsert = sqls.insertTable('zt_content', 'content', "'" + req.body.desc + "'");
    var findLatId = sqls.findLatId('zt_article');
    sqlParamsEntity.push(query.getNewSqlParamEntity(sqlInsert, {}));
    sqlParamsEntity.push(query.getNewSqlParamEntity(findLatId, {}));
    query.execTrans(sqlParamsEntity, function (err, result) {
        if (err) {
            console.error("事务执行失败");
        } else {
            var sqlParamsEntity = [];
            var eachValue = [];
            var feild = 'content_id,title,subtitle,abstr,category_id,' +
                'imgUrl,top,`key`,`status`';
            console.log(req.body, 444);

            eachValue.push("'" + result[0]['insertId'] + "'"); //内容id
            eachValue.push("'" + req.body.title + "'"); //标题
            eachValue.push("'" + req.body.abstrTitle + "'"); //副标题
            eachValue.push("'" + req.body.abstr + "'");  // 概要
            eachValue.push("'" + req.body.type + "'"); //分类
            eachValue.push("'" + req.body.imgSrc + "'"); //图片
            eachValue.push("'" + req.body.top + "'"); //排序
            eachValue.push("'" + req.body.label + "'");  // 标签
            eachValue.push("'" + req.body.status + "'");  // 状态

            var insetContent = sqls.insertTable('zt_article', feild, eachValue);
            console.log(insetContent);
            sqlParamsEntity.push(query.getNewSqlParamEntity(insetContent, {}));
            query.execTrans(sqlParamsEntity, function (err, result1) {
                if (err) {
                    console.error("事务执行失败");
                } else {
                    var results = {'ret': 1};
                    res.send(results);
                }
            });
        }
    });
});

/**
 * 提image
 * **/
app.use('/postImg',function (req,res) {
        console.log(req.query);
});


/*删除文章*/
app.use('/delete',function (req,res) {
    var params = req.query;
    var sqlParamsEntity = [];
    var sql = sqls.fidDateFromTable('zt_article', params['id'], '', 'content_id');
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql, {}));
    query.execTrans(sqlParamsEntity, function (err, result) {
        if (err) {
            console.error("事务执行失败");
        } else {
            var sql1 = sqls.DeleteData('zt_article','id='+params['id']);
            var sql2 = sqls.DeleteData('zt_content', 'id='+result[0][0]['content_id']);
            var sqlParamsEntity = [];
            sqlParamsEntity.push(query.getNewSqlParamEntity(sql1, {}));
            sqlParamsEntity.push(query.getNewSqlParamEntity(sql2, {}));
            query.execTrans(sqlParamsEntity, function (err, result1) {
                if (err) {
                    console.error("事务执行失败");
                } else {
                    var results  ={'ret':1};
                    res.send(results);
                }
            });
        }
    });
});


/*-------------------获取相册类型 start----------------------*/
app.use('/photos',function (req,res) {
    var sql=sqls.findData('zt_photoSort');
    var sqlParamsEntity = [];
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql,{}));
    query.execTrans(sqlParamsEntity,function (err,result) {
        if(err){
            console.error("事务执行失败");
        }else {
            var results  ={'ret':1,'rows':result[0]};
            res.send(results);
        }

    });
});
/*相册添加*/
 app.post('/savePhoto',function (req,res) {
     var eachValue = [];
     var feild = 'title,abstr,url,sort';
     eachValue.push("'" + req.body.title + "'"); //标题
     eachValue.push("'" + req.body.abstr + "'");  // 概要
     eachValue.push("'" + req.body.url + "'"); //图片
     eachValue.push("'" + req.body.sort + "'"); //排序
     let _sql = sqls.insertTable('zt_photo',feild,eachValue);
     var sqlParamsEntity = [];
     sqlParamsEntity.push(query.getNewSqlParamEntity(_sql,{}));
     query.execTrans(sqlParamsEntity,function (err,result) {
          if(err){
              console.error("事务执行失败");
          } else{
              var results  ={'ret':1};
              res.send(results);
          }
     });
 });
/*根据类型获取*/
app.use('/photos',function (req,res) {
    var sql=sqls.findData('zt_photoSort');
    var sqlParamsEntity = [];
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql,{}));
    query.execTrans(sqlParamsEntity,function (err,result) {
        if(err){
            console.error("事务执行失败");
        }else {
            var results  ={'ret':1,'rows':result[0]};
            res.send(results);
        }

    });
});
/*相册类别添加*/
app.use('/getPhotoById',function (req,res) {
    var params = req.query;
    let where = `where sort =${params.id}`;
    let sql=sqls.findData('zt_photo',where);
    let sqlParamsEntity = [];
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql,{}));
    query.execTrans(sqlParamsEntity, function(err, result){
        if(err){
            console.error("事务执行失败");
        }else{
            var results  ={'ret':1,'rows':result[0]};
            res.send(results);
        }
    });
});


/*获取右边固定的信息*/

app.use('/getRight',function (req,res) {
    var sqlParamsEntity = [];
    var sql1 = sqls.countTableLen('article_discuss','');
    var sql2 = sqls.countTableLen('photo_discuss', '');
    var sql3 = sqls.countTableLen('zt_article', '');
    var sql4 = sqls.coTotal('zt_visit');
    var sql5 = sqls.findData('zt_category');
    var sql6 = sqls.findData('zt_article',`where top BETWEEN 1 and 5`);
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql1, {}));
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql2, {}));
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql3, {}));
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql4, {}));
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql5, {}));
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql6, {}));
    query.execTrans(sqlParamsEntity,function (err,result) {
        if(err){
            console.error("事务执行失败");
        }else{
            let params ={};
            params['discuss_len'] = parseInt(result[0][0]['count(1)'])+parseInt(result[1][0]['count(1)']);
                params['visit_len'] = parseInt(parseInt(result[2][0]['count(1)']));
             params['article_len']= parseInt(result[3][0]['sum(count)']);
                params['category']= result[4];
                 params['article']= result[5];
               let results  ={'ret':1,'rows':params};
               res.send(results);
        }
    })

});
/*获取留言信息*/
app.use('/getMessage',function (req,res) {
       /**/
    let params = req.query;
    let sqlParamsEntity = [];
    let page = params['page'];
    let per_page = params['per_page'];
    let where = `INNER JOIN zt_user AS B ON B.id = A.userId WHERE A.type = 0 AND A.r_id = 0`;
    let fields=`A.content,B.username,A.date,A.userId,A.r_id`;
    let sql5 = sqls.coTableData('zt_discuss',where,fields,page,per_page);
    console.log(sql5);
    var where6 = `left JOIN zt_user as B on B.id = A.userId where A.type=0`;
    let sql6 = sqls.coTableData('zt_discuss',where6,fields,page=1,per_page=5);
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql5, {}));
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql6, {}));
    query.execTrans(sqlParamsEntity,function (err,result) {
        console.log(result,78);
        if (err) {
            console.error("事务执行失败");
        } else {
            let params = {};
            params['message'] = result[0];
            params['user'] = result[1];
            let results = {'ret': 1, 'rows': params};
            res.send(results);
        }
    });
});

/*留言*/

app.post('/postMessage',function (req,res) {
    var sqlParamsEntity = [];
    var sql= sqls.findData('zt_user', `where username='${req.body.username}'`);
    sqlParamsEntity.push(query.getNewSqlParamEntity(sql, {}));
    query.execTrans(sqlParamsEntity, function (err, result) {
        if (err) {
            console.error("事务执行失败");
        } else {
            if(result[0].length>0){
                var sqlParamsEntity = [];
                var eachValue = [];
                var feild = 'content,date,userId,r_id,type';
                eachValue.push("'" + req.body.content + "'"); //内容id
                eachValue.push("'" + new Date().Format() + "'"); //标题
                eachValue.push("'" + result[0][0]['id'] + "'"); //副标题
                eachValue.push("'" + req.body.userId + "'");  // 概要
                eachValue.push("'0'");  // 概要
            var insetContent = sqls.insertTable('zt_discuss', feild, eachValue);

            sqlParamsEntity.push(query.getNewSqlParamEntity(insetContent, {}));
            query.execTrans(sqlParamsEntity, function (err, result1) {
                if (err) {
                    console.error("事务执行失败");
                } else {
                    var results = {'ret': 1};
                    res.send(results);
                }
            });
        }else{
                var feild = 'username,email';
                var eachValue = [];
                var sqlParamsEntity = [];
                eachValue.push("'" + req.body.username + "'"); //内容id
                eachValue.push("'" +req.body.email + "'"); //标题
                var insetContent = sqls.insertTable('zt_user', feild, eachValue);
                var find = sqls.findLatId('zt_user');
                sqlParamsEntity.push(query.getNewSqlParamEntity(insetContent, {}));
                sqlParamsEntity.push(query.getNewSqlParamEntity(find, {}));
                query.execTrans(sqlParamsEntity, function (err, result1) {
                    if (err) {
                        console.error("事务执行失败");
                    } else {
                    let lastId = result1[1]['LAST_INSERT_ID()'];
                    var eachValue1=[];
                        var feild = 'content,date,userId,r_id,type';
                        eachValue1.push("'" + req.body.content + "'"); //内容id
                        eachValue1.push("'" + new Date().Format() + "'"); //标题
                        eachValue1.push("'" + lastId + "'"); //副标题
                        eachValue1.push("'" + req.body.userId + "'");  // 概要
                        eachValue1.push("'0'");  // 概要
                        var insetContent = sqls.insertTable('zt_discuss', feild, eachValue1);
                        console.log(insetContent,4555);
                        sqlParamsEntity.push(query.getNewSqlParamEntity(insetContent, {}));
                        query.execTrans(sqlParamsEntity, function (err, result1) {
                            if (err) {
                                console.error("事务执行失败");
                            } else {
                                var results = {'ret': 1};
                                res.send(results);
                            }
                        });
                 }
                })
            }
        }
    });
});


Date.prototype.Format = function() { //author: meizz
    let d = new Date();
    let times=d.getFullYear() + '-' + (d.getMonth() + 1) + '-' + d.getDate() + ' ' + d.getHours() + ':' + d.getMinutes() + ':' + d.getSeconds();
    return times;
};
/*-------------------获取相册类型 end----------------------*/

var server  =app.listen(8080,'127.0.0.1',function () {
    var host=server.address().address;
    var port=server.address().port;
    console.log(host,port);
         console.log(35555);
});
